package com.yfbao.horizon.docker.service;

import com.yfbao.horizon.docker.entity.EntityBean;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Slf4j
@Service
public class RuleUpdateService {

    @Autowired
    private DataSource dataSource;

    private Connection connection = null;

    private static String dateSourceType="";
    private Object obj = new Object();

    @PostConstruct
    private void init() {
        if (null == connection) {
            synchronized (obj) {
                if (null == connection) {
                    try {
                        this.connection = dataSource.getConnection();
                        log.info("Rule构造connection成功");
                    } catch (SQLException e) {
                        log.error("get database connect error", e);
                    }
                }
            }
            initDateSourceType();
            log.info("数据库类型："+dateSourceType);
        }
    }

    private void initDateSourceType(){
        try {
            String url = connection.getMetaData().getURL().toLowerCase().trim();
            if(url.contains("jdbc:mysql")){
                dateSourceType = "mysql";
            }else if(url.contains("jdbc:oracle")){
                dateSourceType = "oracle";
            }else if(url.contains("jdbc:postgresql")){
                dateSourceType = "postgresql";
            }else if(url.contains("jdbc:sqlserver")){
                dateSourceType = "sqlserver";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        dateSourceType = "mysql";
    }

    public Boolean insertBySelect(){
        String insertSqlTemp = "insert into ctp_user_resource_update(" +
                "id,tenant_id,create_time,update_time,version,user_id,role_id,org_id,origin_id,resource_id," +
                "resource_code,resource_name,resource_icon,resource_parent_id,resource_level,resource_path," +
                "resource_url,resource_open_type,resource_admin,resource_allow_end,resource_sort_number," +
                "resource_app_id,resource_entity_full_name,resource_type,resource_client_type,resource_data_type," +
                "resource_permission_type,action,resource_org_id,member_post_id,effective_time,invalid_time,is_enable,creator_id" +
                ") " +
                "select id,tenant_id,create_time,update_time,version,user_id,role_id,org_id,origin_id,resource_id," +
                "resource_code,resource_name,resource_icon,resource_parent_id,resource_level,resource_path," +
                "resource_url,resource_open_type,resource_admin,resource_allow_end,resource_sort_number," +
                "resource_app_id,resource_entity_full_name,resource_type,resource_client_type,resource_data_type," +
                "resource_permission_type,action,resource_org_id,member_post_id,effective_time,invalid_time,is_enable,creator_id" +
                " from ctp_user_resource where origin_id = -1 and role_id != -1 and resource_type=3";
        Statement statement = null;
        try {
            log.info("begin query: " + insertSqlTemp);
            statement = connection.createStatement();
            int i = statement.executeUpdate(insertSqlTemp);
            log.info("insert select status:"+i );
            return i>0;
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("ddl操作异常", e);
        } finally {
            try {
                if (null != statement) {
                    statement.close();
                }
            } catch (SQLException e) {
                log.error("关闭ResultSet or statement异常", e);
            }
        }
        return false;
    }

    public Integer getEmptyList() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from ctp_user  where name ='asdasdasdasdasdas' ");
        Statement statement = null;
        ResultSet rs = null;
        int rowNum = 0;
        try {
            statement = connection.createStatement();
            log.info("rul sql:"+sql.toString());
            rs = statement.executeQuery(sql.toString());
            while (rs.next()){
                rowNum++;
            }
            log.info("rule关联数据条数查询结果为：" + rowNum);
            return rowNum;
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("查询关联数据条数异常", e);
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                if (null != statement) {
                    statement.close();
                }
            } catch (SQLException e) {
                log.error("查询表，关闭ResultSet or statement异常", e);
            }
        }
        return rowNum;
    }


    public Integer getCount() {
        StringBuilder sql = new StringBuilder();
        sql.append("select  count(*) as num from ")
                .append(" ctp_authorization_object a, ")
                .append(" ctp_authorization_rule b, ")
                .append(" ctp_authorization_rule_func c, ")
                .append(" ctp_role_resource d, ")
                .append(" ctp_resource e ")
                .append(" where a.rule_id=b.id AND b.id=c.rule_id and  b.is_deleted=0  and c.role_id = d.role_id and d.resource_id=e.id and e.data_status=1 ; ");
        Statement statement = null;
        ResultSet rs = null;
        int rowNum = 0;
        try {
            statement = connection.createStatement();
            log.info("rul sql:"+sql.toString());
            rs = statement.executeQuery(sql.toString());
            while (rs.next()){
                rowNum = rs.getInt("num");
            }
            log.info("rule关联数据条数查询结果为：" + rowNum);
            return rowNum;
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("查询rule关联数据条数异常", e);
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                if (null != statement) {
                    statement.close();
                }
            } catch (SQLException e) {
                log.error("查询表，关闭ResultSet or statement异常", e);
            }
        }
        return rowNum;
    }

    public String findTablesCount() {
        List<String> tables = new ArrayList<>();
        tables.add("ctp_authorization_object");
        tables.add("ctp_authorization_rule");
        tables.add("ctp_authorization_rule_func");
        tables.add("ctp_role_resource");
        tables.add("ctp_resource");

        StringBuilder builder = new StringBuilder();
        for (String table : tables) {
            Integer tableCount = getTableCount(table);
            builder.append("表:" + table).append(",").append("记录数：").append(tableCount).append(";\n");
        }
        return builder.toString();
    }

    private Integer getTableCount(String tableName) {
        String sql = " select count(*) as num from " + tableName;
        Statement statement = null;
        ResultSet rs = null;
        int rowNum = 0;
        try {
            statement = connection.createStatement();
            rs = statement.executeQuery(sql);
            while (rs.next()){
                rowNum = rs.getInt("num");
            }
            log.info("表名：" + tableName + ", 记录数：" + rowNum);
            return rowNum;
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("查询表数据异常", e);
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                if (null != statement) {
                    statement.close();
                }
            } catch (SQLException e) {
                log.error("查询表，关闭ResultSet or statement异常", e);
            }

        }
        return rowNum;
    }


    public Integer updateEntity(Integer partNum){
        if( null==partNum || 0==partNum){
            //一次性全量升级
            List<EntityBean> sourceData = findSourceData(null, null);
            if(sourceData ==null){
                log.error("find sourceData error");
                return 0;
            }
            Integer updNum = batchInsertEntity(sourceData);
            log.info("success,need update:"+updNum+", total update:"+updNum);
            return updNum;
        }

        if(partNum < 1000){
            List<EntityBean> sourceData = findSourceData(0, partNum);
            if(sourceData ==null){
                log.error("find sourceData error");
                return 0;
            }
            Integer updNum = batchInsertEntity(sourceData);
            log.info("验证 success update:"+updNum+", total update:"+updNum);
            return updNum;
        }

        int start = 0;
        int total=0;
        while (true){
            List<EntityBean> sourceData = findSourceData(start,  partNum);
            if(sourceData ==null){
                log.error("find sourceData error");
                return 0;
            }
            if(sourceData.isEmpty()){
                log.info("finish all data");
                break;
            }
            if( sourceData.size() == partNum){
                start = start+partNum;
            }else {
                start = start+ sourceData.size();
            }
            Integer updNum = batchInsertEntity(sourceData);
            total = total+updNum;
            log.info("success,need update:"+sourceData.size()+",update size:"+updNum+", total update:"+total);
            if(updNum != sourceData.size()){
                //TODO: 异常记录
                log.warn(" update size not equal need size,stop....");
                break;
            }
        }
        return total;
    }

    private Integer batchInsertEntity(List<EntityBean> sourceDatas){

        String sqlStart ="insert into ctp_entity_resource (" +
                " id,tenant_id,create_time,update_time,version,entity_id,entity_type,include_child,role_id,org_id,origin_id,resource_id," +
                " resource_code,resource_name,resource_icon,resource_parent_id,resource_level,resource_path,resource_url,resource_open_type," +
                " resource_admin,resource_allow_end,resource_sort_number,resource_app_id,resource_entity_full_name," +
                " resource_type,resource_client_type,resource_data_type,resource_permission_type,action,resource_org_id,creator_id) values ";
        StringBuilder builder = new StringBuilder();
        builder.append(sqlStart);
        int rowNum = 0;
        int insertTotal =0;
        String sql=null;
        log.info("本次待新增数据量："+sourceDatas.size());
        for (EntityBean sourceData : sourceDatas) {
            rowNum++;
            builder.append(" (");
            //todo: 中间赋值
            String s = factoryValue(sourceData);
            builder.append(s);
            builder.append(" ),");
            if(rowNum>=10000){
                int length = builder.length();
                if(builder.substring(length-1,length).equals(",")){
                    sql = builder.substring(0, length - 1);
                    Integer num = batchInsert(sql);
                    builder = new StringBuilder();
                    builder.append(sqlStart);
                    if(num ==0 || num<0){
                        log.error("insert into db failed,stop...");
                        break;
                    }
                    insertTotal = insertTotal+num;
                    rowNum=0;
                    log.info("成功写入数据量："+num+", 累计写入数据量:"+insertTotal);
                }
            }
        }
        int length = builder.length();
        if(builder.substring(length-1,length).equals(",")){
            sql = builder.substring(0, length - 1);
            Integer num = batchInsert(sql);
            insertTotal = insertTotal+num;
            log.info("成功写入数据量："+num+", 累计写入数据量:"+insertTotal);
        }
        return insertTotal;
    }

    private Integer batchInsert(String sql){
        Statement statement = null;
        try {
            statement = connection.createStatement();
            int num = statement.executeUpdate(sql);
            return num;
        } catch (SQLException e) {
            log.error("查询表数据异常", e);
            return -1;
        } finally {
            try {
                if (null != statement) {
                    statement.close();
                }
            } catch (SQLException e) {
                log.error("查询表，关闭ResultSet or statement异常", e);
            }
        }
    }

    private String factoryValue(EntityBean sourceData ){
        StringBuilder builder = new StringBuilder();
        builder.append(sourceData.getId()).append(",");
        builder.append(sourceData.getTenantId()).append(",");
        builder.append("'").append(sourceData.getCreateTime()).append("'").append(",");
        builder.append("'").append(sourceData.getUpdateTime()).append("'").append(",");
        builder.append(sourceData.getVersion()).append(",");
        builder.append(sourceData.getEntityId()).append(",");
        builder.append("'").append(sourceData.getEntityType()).append("'").append(",");
        builder.append(Integer.valueOf(sourceData.getIncludeChild() ? 1:0)).append(",");
        builder.append(sourceData.getRoleId()).append(",");
        builder.append(sourceData.getOrgId()).append(",");
        builder.append(sourceData.getOriginId()).append(",");
        builder.append(sourceData.getResourceId()).append(",");
        builder.append("'").append(sourceData.getResourceCode()).append("'").append(",");
        builder.append("'").append(sourceData.getResourceName()).append("'").append(",");
        builder.append("'").append(sourceData.getResourceIcon()).append("'").append(",");
        builder.append(sourceData.getResourceParentId()).append(",");
        builder.append(sourceData.getResourceLevel()).append(",");
        builder.append("'").append(sourceData.getResourcePath()).append("'").append(",");
        builder.append("'").append(sourceData.getResourceUrl()).append("'").append(",");
        builder.append("'").append(sourceData.getResourceOpenType()).append("'").append(",");
        builder.append(Integer.valueOf(sourceData.getResourceAdmin() ? 1:0)).append(",");
        builder.append(Integer.valueOf(sourceData.getResourceAllowEnd() ? 1:0)).append(",");
        builder.append(sourceData.getResourceSortNumber()).append(",");
        builder.append("'").append(sourceData.getResourceAppId()).append("'").append(",");
        builder.append("'").append(sourceData.getResourceEntityFullName()).append("'").append(",");
        builder.append(sourceData.getResourceType()).append(",");
        builder.append(sourceData.getResourceClientType()).append(",");
        builder.append("'").append(sourceData.getResourceDataType()).append("'").append(",");
        builder.append("'").append(sourceData.getResourcePermissionType()).append("'").append(",");
        builder.append(sourceData.getAction()).append(",");
        builder.append(sourceData.getResourceOrgId()).append(",");
        builder.append(sourceData.getCreatorId());
        return builder.toString();
    }

    private List<EntityBean> findSourceData(Integer limitStart,Integer limitEnd) {
        StringBuilder builder = new StringBuilder();
        builder.append(" select ")
                .append("  a.tenant_id as tenant_id ,a.entity_id as entity_id,a.entity_type as entity_type,a.include_child as include_child,")
                .append("    c.role_id as role_id, c.org_id as org_id, b.id as origin_id,")
                .append("    e.id as resource_id, ")
                .append("    e.`code` as resource_code,e.name as resource_name,e.icon as resource_icon,e.parent_id as resource_parent_id,e.resource_level as resource_level,e.path as resource_path,")
                .append("    e.url as resource_url, e.open_type as resource_open_type, e.is_admin as resource_admin,e.allow_end as resource_allow_end,e.sort_number as resource_sort_number,e.app_id as resource_app_id,")
                .append("    e.entity_full_name as resource_entity_full_name,e.type as resource_type, e.client_type as resource_client_type, e.data_type as resource_data_type, 'use' as resource_permission_type,")
                .append("    b.action as action, e.org_id as resource_org_id, b.creator_id as creator_id ")
                .append(" from ")
                .append(" ctp_authorization_object a,")
                .append(" ctp_authorization_rule b,")
                .append(" ctp_authorization_rule_func c,")
                .append(" ctp_role_resource d,")
                .append(" ctp_resource e")
                .append(" where a.rule_id=b.id AND b.id=c.rule_id and  b.is_deleted=0 ")
                .append(" and c.role_id = d.role_id and d.resource_id=e.id  and e.data_status=1 ");
        if (null != limitStart && null != limitEnd) {
            builder.append(" limit ").append(limitStart).append(",").append(limitEnd);
        }

        Statement statement = null;
        ResultSet rs = null;
        try {
            statement = connection.createStatement();
            rs = statement.executeQuery(builder.toString());

            List<EntityBean> list =new ArrayList<>();
            while (rs.next()){
                EntityBean entityBean = new EntityBean();
                entityBean.setTenantId(rs.getLong("tenant_id"));
                entityBean.setEntityId(rs.getLong("entity_id"));
                entityBean.setEntityType(rs.getString("entity_type"));
                entityBean.setIncludeChild(rs.getBoolean("include_child"));
                entityBean.setRoleId(rs.getLong("role_id"));
                entityBean.setOrgId(rs.getLong("org_id"));
                entityBean.setOriginId(rs.getLong("origin_id"));
                entityBean.setResourceId(rs.getLong("resource_id"));
                entityBean.setResourceCode(rs.getString("resource_code"));
                entityBean.setResourceName(rs.getString("resource_name"));
                entityBean.setResourceIcon(rs.getString("resource_icon"));
                entityBean.setResourceParentId(rs.getLong("resource_parent_id"));
                entityBean.setResourceLevel(rs.getInt("resource_level"));
                entityBean.setResourcePath(rs.getString("resource_path"));
                entityBean.setResourceUrl(rs.getString("resource_url"));
                entityBean.setResourceOpenType(rs.getString("resource_open_type"));
                entityBean.setResourceAdmin(rs.getBoolean("resource_admin"));
                entityBean.setResourceAllowEnd(rs.getBoolean("resource_allow_end"));
                entityBean.setResourceSortNumber(rs.getInt("resource_sort_number"));
                entityBean.setResourceAppId(rs.getString("resource_app_id"));
                entityBean.setResourceEntityFullName(rs.getString("resource_entity_full_name"));
                entityBean.setResourceType(rs.getInt("resource_type"));
                entityBean.setResourceClientType(rs.getInt("resource_client_type"));
                entityBean.setResourceDataType(rs.getString("resource_data_type"));
                entityBean.setResourcePermissionType(rs.getString("resource_permission_type"));
                entityBean.setAction(rs.getInt("action"));
                entityBean.setResourceOrgId(rs.getLong("resource_org_id"));
                entityBean.setCreatorId(rs.getLong("creator_id"));

                list.add(entityBean);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("查询表数据异常", e);
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                if (null != statement) {
                    statement.close();
                }
            } catch (SQLException e) {
                log.error("查询表，关闭ResultSet or statement异常", e);
            }

        }
        return null;
    }






//     builder.append("insert into ctp_entity_resource (tenant_id,create_time,update_time,version,entity_id,entity_type,include_child,role_id,org_id,origin_id,resource_id,resource_code,resource_name,resource_icon,")
//             .append(" resource_parent_id,resource_level,resource_path,resource_url,resource_open_type,resource_admin,resource_allow_end,resource_sort_number,resource_app_id,resource_entity_full_name,")
//                .append(" resource_type,resource_client_type,resource_data_type,resource_permission_type,action,resource_org_id,creator_id) ")
//                .append("select ")
//                .append("  a.tenant_id as tenant_id ,a.create_time as create_time, a.update_time as update_time,a.version as version ,a.entity_id as entity_id,a.entity_type as entity_type,a.include_child as include_child,")
//                .append("    c.role_id as role_id, c.org_id as org_id, b.id as origin_id,")
//                .append("    e.id as resource_id, ")
//                .append("    e.`code` as resource_code,e.name as resource_name,e.icon as resource_icon,e.parent_id as resource_parent_id,e.resource_level as resource_level,e.path as resource_path,")
//                .append("    e.url as resource_url, e.open_type as resource_open_type, e.is_admin as resource_admin,e.allow_end as resource_allow_end,e.sort_number as resource_sort_number,e.app_id as resource_app_id,")
//                .append("    e.entity_full_name as resource_entity_full_name,e.type as resource_type, e.client_type as resource_client_type, e.data_type as resource_data_type, 'use' as resource_permission_type,")
//                .append("    b.action as action, e.org_id as resource_org_id, b.creator_id as creator_id ")
//                .append("from ")
//                .append("ctp_authorization_object a,")
//                .append("ctp_authorization_rule b,")
//                .append("ctp_authorization_rule_func c,")
//                .append("ctp_role_resource d,")
//                .append("ctp_resource e")
//                .append("where a.rule_id=b.id AND b.id=c.rule_id and  b.is_deleted=0 ")
//                .append(" and c.role_id = d.role_id and d.resource_id=e.id  and e.data_status=1 ");
}
